﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace VPLink2013.Framework
{
    public static class VPLink2013SqlHelper
    {
        #region Get value from Reader
        public static string GetValueReader(SqlDataReader Reader, int position)
        {
            string strValue = "";
            if (!Reader.IsDBNull(position))
            {
                strValue = Reader.GetValue(position).ToString();
            }
            return strValue;
        }
        public static int GetValueReaderInt(SqlDataReader Reader, int position)
        {
            int intValue = 0;
            if (!Reader.IsDBNull(position))
            {
                intValue = Reader.GetInt32(position);
            }
            return intValue;
        }
        public static bool GetValueReaderBit(SqlDataReader Reader, int position)
        {
            bool blnValue = false;
            if (!Reader.IsDBNull(position))
            {
                blnValue = Reader.GetBoolean(position);
            }
            return blnValue;
        }
        public static short GetValueReaderSmallint(SqlDataReader Reader, int position)
        {
            short shoValue = 0;
            if (!Reader.IsDBNull(position))
            {
                shoValue = Reader.GetInt16(position);
            }
            return shoValue;
        }
        public static float GetValueReaderFloat(SqlDataReader Reader, int position)
        {
            float fltValue = 0;
            if (!Reader.IsDBNull(position))
            {
                fltValue = Reader.GetFloat(position);
            }
            return fltValue;
        }
        public static double GetValueReaderDouble(SqlDataReader Reader, int position)
        {
            double dblValue = 0;
            if (!Reader.IsDBNull(position))
            {
                dblValue = Reader.GetDouble(position);
            }
            return dblValue;
        }
        public static decimal GetValueReaderDecimal(SqlDataReader Reader, int position)
        {
            decimal decValue = 0;
            if (!Reader.IsDBNull(position))
            {
                decValue = Reader.GetDecimal(position);
                //return decValue.ToString("##0.0");
            }
            return decValue;
        }
        public static decimal GetValueReaderMoney(SqlDataReader Reader, int position)
        {
            decimal decValue = 0;
            if (!Reader.IsDBNull(position))
            {
                decValue = Reader.GetDecimal(position);
            }
            return decValue;
        }
        public static string GetValueReaderDate(SqlDataReader Reader, int position)
        {
            string strValue = "";
            if (!Reader.IsDBNull(position))
            {
                strValue = Reader.GetDateTime(position).ToString("dd/MM/yyyy");
            }
            return strValue;
        }
        public static DateTime GetValueReaderDateTime(SqlDataReader Reader, int position)
        {
            DateTime dtValue = new DateTime(2001, 1, 1);
            if (!Reader.IsDBNull(position))
            {
                dtValue = Reader.GetDateTime(position);
            }
            return dtValue;
        }
        public static string GetValueReaderHeure(SqlDataReader Reader, int position)
        {
            string strValue = "";
            if (!Reader.IsDBNull(position))
            {
                strValue = Reader.GetDateTime(position).ToString("HH:mm:ss");
            }
            return strValue;
        }
        #endregion

        #region SQL : connection, command, execute

        public static SqlConnection GetConnection(string strConnection)
        {
            SqlConnection conn = new SqlConnection(strConnection);
            try
            {
                conn.Open();
                ContextTrace.AddTrace("strConnection : " + strConnection);
            }
            catch (System.Exception exc)
            {
                //ContextTrace.AddTrace("Error.GetConnection : " + exc.Message + "/" + exc.StackTrace);
            }
            return conn;
        }
        public static SqlCommand GetCommand(SqlConnection conn, string strSql)
        {
            SqlCommand cmd = new SqlCommand();
            try
            {
                cmd.CommandType = CommandType.Text;
                cmd.Connection = conn;
                cmd.CommandText = strSql;
                //ContextTrace.AddTrace("GetCommand.strSql : " + strSql);
            }
            catch (System.Exception exc)
            {
                //ContextTrace.AddTrace("Error.GetCommand : " + exc.Message + "/" + exc.StackTrace);
            }
            return cmd;
        }
        public static void ExecuteNonQuery(SqlConnection conn, SqlCommand cmd)
        {
            try
            {
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                conn.Close();
            }
            catch (System.Exception exc)
            {
                //ContextTrace.AddTrace("Error.ExecuteNonQuery : " + exc.Message + "/" + exc.StackTrace);
            }
        }
        public static object ExecuteScalar(SqlConnection conn, SqlCommand cmd)
        {
            try
            {
                object obj = cmd.ExecuteScalar();
                cmd.Dispose();
                conn.Close();
                return obj;
            }
            catch (System.Exception exc)
            {
                //ContextTrace.AddTrace("Error.ExecuteNonQuery : " + exc.Message + "/" + exc.StackTrace);
            }
            return null;
        }
        public static void CloseReader(SqlConnection conn, SqlDataReader reader)
        {
            try
            {
                reader.Close();
                reader.Dispose();
                conn.Close();
            }
            catch (System.Exception exc)
            {
                //ContextTrace.AddTrace("Error.ExecuteNonQuery : " + exc.Message + "/" + exc.StackTrace);
            }
        }
        #endregion SQL : connection, command, execute
    }
}
